This reading provides an overview of the online analytical processing (or OLAP) cube as a multi-dimensional array of data and outlines how it is used in practice.

Overview

Data warehouses aggregate and integrate data from multiple data sources. This data is imported from Online Transactional Processing (OLTP) database systems, where data is structured to manage the day-to-day business operations in real time.

For example, Global Super Store sells products online to the entire world. So, it requires an OLTP database system to collect, update, retrieve and backup customers, products and orders data. This type of database is not very effective at performing data analytics.

To perform effective data analysis on OLTP databases, you need to restructure your data in Online Analytical Processing (OLAP) database systems that are created mainly for data analysis. Key advantages of OLAP database systems include the following:

These features enable data analysts to provide an optimized level of data analytics that help their audiences obtain answers to their questions about the performance of the business. Along this line, OLAP database systems utilize the cube structure to visualize the multidimensional data concept. OLAP cube is a multidimensional array used to store and maintain data to provide advanced level of data analysis with more interesting insights.

OLAP Cube in practice

To perform data analysis, you need to be clear about which business activities you want to examine.  You also need to know which measures and dimensions are required to provide you with the most meaningful and useful information.

For example, Global Super Store sells furniture, office supplies and technology products around the world. They want to investigate the sales performance of different product categories over the last four years across four different European countries: France, Germany, Italy and the UK.

One solution is to analyze the company data in a basic way, where you provide Global Super Stores with three simple tables of data that show total sales made in each country, total sales made in each year, and total sales made in each category, as shown below.

In each table of data, you are showing the total sales in the context of one dimension as follows:

  1. Sales in the context of the Time dimension (Year – 2019 , 2020 , 2021 and 2022 ).

  2. Sales in the context of the Location dimension (Country - United Kingdom , Italy , France and Germany ).

  3. Sales in the context of the Product dimension (Category - Furniture , Office Supplies and Technology ).

However, this kind of analysis does not provide any deep insights into your data. And it is not that easy to investigate and compare data based on multiple dimensions, (which is required to facilitate more interesting information for the business decision makers). This is where multidimensional data analysis can play a major role with the OLAP cube.

In this situation, you can merge the three dimensions (Time, Location and Product) together in a multidimensional cube as shown below.

You can now use the OLAP cube to analyze complex data more efficiently. You can then find answers for any questions you have about business sales operations based on different dimensions (like product, time and location) at the same time.

For example, the following chart combines the three dimensions to demonstrate Global Super Store’s sales performance in terms of all categories of products, over the past four years, across the four specified countries:

In the above multidimensional chart, you are viewing the same data presented earlier. However, now you can easily compare data. You can now spot for example, that sales are strongest in France across all categories of products, whereas Italy has the lowest sales. You may have also noticed that there is an increase in sales every year.

As you can see, the multidimensional cube facilitates a more efficient method of providing a more meaningful view of information. This lets you interpret and compare data easier. In addition, you can use the OLAP cube to perform different types of useful operations such as Drill-down, Roll-up, Slice, Dice and Pivot. Let us have a look into some of these operations.

Slice

A Slice operation focuses on a sub dimension from the cube, which provides a more focused view of information. In the following example, the cube focuses on the furniture category of the product dimension.

The chart below shows how the slice operation is used to address the question: “How have sales in the furniture category performed over the previous four years in each country?”

Pivot

A Pivot operation provides an alternative view of data by rotating the axes of the cube. For example, the following cube rotates the Location and Time dimensions.

Data in the previous chart (Slice) is now presented differently. Location and Time dimensions are swapped. Country data is now represented in the first column and the Years data is represented in the row, as shown below.

Dice

The dice operation emphasizes two or more sub dimensions from the cube as shown below.

The following chart shows how the dice operation is used to address the question: “How have sales of furniture and technology products performed over the last two years in France and Germany?”

Drill-down and roll-up

The multidimensional cube model also supports the hierarchical data structure. This allows you to drill down into a more specific area of data or roll-up to move into a higher level of data. The following chart, for example, uses the drill down operation to show data in quarters instead of years.

Conclusion

OLAP cube is a multidimensional array of data used in Online analytical database systems to provide better insights. You can use the OLAP cube to perform different types of useful operations such as Drill-down, Roll-up, Slice, Dice and Pivot. These operations help you to analyze data from different perspectives. You should now be familiar with how the OLAP cube functions as a multi-dimensional array of data and how it is used in practice.